package cn.edu.klmy.dao.impl;

import cn.edu.klmy.dao.AbstractJdbcTemplate;
import cn.edu.klmy.dao.IScheduleDao;
import cn.edu.klmy.models.clasz.Clasz;
import cn.edu.klmy.models.schedule.*;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 顾伟
 *         创建日期：13-4-19
 */
@Repository
public class ScheduleDao extends AbstractJdbcTemplate implements IScheduleDao {

    public List<CourseScheduling> getAllCourseSchedule() {
        String SCHEDULE_QUERY = "SELECT A.COURSE_SCHEDULING_ID,A.RESTRICTION_NUMBER,A.RESTRICTION_TERM,A.RESTRICTION_GRADE," +
                "A.RESTRICTION_WEEK,A.RESTRICTION_TIME,A.COURSE_ID,A.TEACHER_ID,A.CLASSROOM_ID," +
                "A.RESTRICTION_SECTION,A.RESTRICTION_REQUIRED,B.COURSE_NAME,C.TEACHER_NAME,D.CLASSROOM_LOCATION" +
                " FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE B ON A.COURSE_ID=B.ID " +
                "LEFT JOIN TEACHER C ON A.TEACHER_ID=C.ID " +
                "LEFT JOIN CLASSROOM D ON A.CLASSROOM_ID=D.ID ";
        List<CourseScheduling> courseSchedulingList = this.jdbcTemplate.query(SCHEDULE_QUERY, new CourseScheduleRowMapper());
        return courseSchedulingList;
    }

    public List<SituationCourseScheduling> getCurrentCourseScheduleByConditions(int term,int section,String courseName,String teacherName){
        String SCHEDULE_QUERY= "SELECT A.COURSE_SCHEDULING_ID,A.RESTRICTION_NUMBER," +
                "B.COURSE_NAME,C.TEACHER_NAME,D.CLASSROOM_LOCATION" +
                " FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE B ON A.COURSE_ID=B.ID " +
                "LEFT JOIN TEACHER C ON A.TEACHER_ID=C.ID " +
                "LEFT JOIN CLASSROOM D ON A.CLASSROOM_ID=D.ID " +
                "WHERE A.RESTRICTION_TERM&" + term + "=" + term +
                " AND A.RESTRICTION_SECTION&" + section + "=" + section;

        if ((null!=courseName&&!courseName.equals("")) || (null!=teacherName && !teacherName.equals(""))) {
            SCHEDULE_QUERY += " AND ";
            if (null!=courseName&&!courseName.equals("")) {
                SCHEDULE_QUERY += "B.COURSE_NAME LIKE '%" + courseName+"%'";
                if (null!=teacherName && !teacherName.equals("")) {
                    SCHEDULE_QUERY += " AND C.TEACHER_NAME LIKE '%" + teacherName+"%'";
                }
            } else {
                if (null!=teacherName && !teacherName.equals("")) {
                    SCHEDULE_QUERY += "C.TEACHER_NAME LIKE '%" + teacherName+"%'";
                }
            }
        }

        List<SituationCourseScheduling> courseSchedulingList = this.jdbcTemplate.query(SCHEDULE_QUERY, new SituationCourseSchedulingRowMapper());
        return courseSchedulingList;
    }

    public List<SituationCourseScheduling> getCurrentCourseSchedule(int term,int section){
        String SCHEDULE_QUERY= "SELECT A.COURSE_SCHEDULING_ID,A.RESTRICTION_NUMBER," +
                "B.COURSE_NAME,C.TEACHER_NAME,D.CLASSROOM_LOCATION" +
                " FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE B ON A.COURSE_ID=B.ID " +
                "LEFT JOIN TEACHER C ON A.TEACHER_ID=C.ID " +
                "LEFT JOIN CLASSROOM D ON A.CLASSROOM_ID=D.ID " +
                "WHERE A.RESTRICTION_TERM&" + term + "=" + term +
                " AND A.RESTRICTION_SECTION&" + section + "=" + section;
        List<SituationCourseScheduling> courseSchedulingList = this.jdbcTemplate.query(SCHEDULE_QUERY, new SituationCourseSchedulingRowMapper());
        return courseSchedulingList;
    }

    public RestrictionTime getRestrictionTimeRule(){
        String sql="SELECT DISTINCT RESTRICTION_TIME FROM COURSE_SCHEDULING";
        List<Integer> restrictionTimeList=this.jdbcTemplate.queryForList(sql,Integer.class);
        RestrictionTime restrictionTime=new RestrictionTime();
        for(Integer courseTime:restrictionTimeList) {
            if((courseTime&1)==1){
                restrictionTime.setHasFirstLesson(true);
            }else if((courseTime&2)==2){
                restrictionTime.setHasSecondLesson(true);
            }else if((courseTime&4)==4){
                restrictionTime.setHasThirdLesson(true);
            }else if((courseTime&8)==8){
                restrictionTime.setHasForthLesson(true);
            }else if((courseTime&16)==16){
                restrictionTime.setHasFifthLesson(true);
            }else if((courseTime&32)==32){
                restrictionTime.setHasSixthLesson(true);
            }else if((courseTime&64)==64){
                restrictionTime.setHasSeventhLesson(true);
            }else if((courseTime&128)==128){
                restrictionTime.setHasEighthLesson(true);
            }
        }
        return restrictionTime;
    }

    public List<CourseScheduling> getCourseScheduleByConditions(String courseName, String teacherName) {
        String SCHEDULE_QUERY = "SELECT A.COURSE_SCHEDULING_ID,A.RESTRICTION_NUMBER,A.RESTRICTION_TERM,A.RESTRICTION_GRADE," +
                "A.RESTRICTION_WEEK,A.RESTRICTION_TIME,A.COURSE_ID,A.TEACHER_ID,A.CLASSROOM_ID," +
                "A.RESTRICTION_SECTION,A.RESTRICTION_REQUIRED,B.COURSE_NAME,C.TEACHER_NAME,D.CLASSROOM_LOCATION" +
                " FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE B ON A.COURSE_ID=B.ID " +
                "LEFT JOIN TEACHER C ON A.TEACHER_ID=C.ID " +
                "LEFT JOIN CLASSROOM D ON A.CLASSROOM_ID=D.ID ";
        if ((null!=courseName&&!courseName.equals("")) || (null!=teacherName && !teacherName.equals(""))) {
            SCHEDULE_QUERY += " WHERE ";
            if (null!=courseName&&!courseName.equals("")) {
                SCHEDULE_QUERY += "B.COURSE_NAME LIKE '%" + courseName+"%'";
                if (null!=teacherName && !teacherName.equals("")) {
                    SCHEDULE_QUERY += " AND C.TEACHER_NAME LIKE '%" + teacherName+"%'";
                }
            } else {
                if (null!=teacherName && !teacherName.equals("")) {
                    SCHEDULE_QUERY += "C.TEACHER_NAME LIKE '%" + teacherName+"%'";
                }
            }
        }
        List<CourseScheduling> courseSchedulingList = this.jdbcTemplate.query(SCHEDULE_QUERY, new CourseScheduleRowMapper());
        return courseSchedulingList;
    }

    public int addCourseSchedule(CourseScheduling courseScheduling) {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(this.jdbcTemplate);
        insert.withTableName("COURSE_SCHEDULING");
        insert.setGeneratedKeyName("COURSE_SCHEDULING_ID");
        insert.compile();

        Map<String, Object> args = new HashMap<String, Object>();
        int courseId = courseScheduling.getCourseId();
        if (courseId != 0)
            args.put("COURSE_ID", courseId);
        int classroomId = courseScheduling.getClassroomId();
        if (classroomId != 0)
            args.put("CLASSROOM_ID", classroomId);
        int teacherId = courseScheduling.getTeacherId();
        if (teacherId != 0)
            args.put("TEACHER_ID", teacherId);

        int term= (courseScheduling.getSecondTerm()<<1)
                +(courseScheduling.getFirstTerm());
        int section=(courseScheduling.getThirdSection()<<2)
                +(courseScheduling.getSecondSection()<<1)
                +courseScheduling.getFirstSection();
        int week=(courseScheduling.getSunday()<<6)
                +(courseScheduling.getSaturday()<<5)
                +(courseScheduling.getFriday()<<4)
                +(courseScheduling.getThursday()<<3)
                +(courseScheduling.getWednesday()<<2)
                +(courseScheduling.getTuesday()<<1)
                +courseScheduling.getMonday();
        int time=(courseScheduling.getEighthLesson()<<7)
                +(courseScheduling.getSeventhLesson()<<6)
                +(courseScheduling.getSixthLesson()<<5)
                +(courseScheduling.getFifthLesson()<<4)
                +(courseScheduling.getForthLesson()<<3)
                +(courseScheduling.getThirdLesson()<<2)
                +(courseScheduling.getSecondLesson()<<1)
                +courseScheduling.getFifthLesson();

        args.put("RESTRICTION_NUMBER", courseScheduling.getRestrictionNumber());
        args.put("RESTRICTION_GRADE", courseScheduling.getRestrictionGrade());
        args.put("RESTRICTION_REQUIRED", courseScheduling.getRequired() == 1 ? true : false);
        args.put("RESTRICTION_TERM",term);
        args.put("RESTRICTION_SECTION",section);
        args.put("RESTRICTION_WEEK",week);
        args.put("RESTRICTION_TIME",time);
        Number insertId = insert.executeAndReturnKey(args);
        return insertId.intValue();
    }

    public void deleteCourseSchedule(int id) {
        String SCHEDULE_DELETE = "DELETE FROM COURSE_SCHEDULING WHERE COURSE_SCHEDULING_ID=" + id;
        this.jdbcTemplate.update(SCHEDULE_DELETE);
    }

    public void updateCourseSchedule(CourseScheduling courseScheduling) {
        int term= (courseScheduling.getSecondTerm()<<1)
                +(courseScheduling.getFirstTerm());
        int section=(courseScheduling.getThirdSection()<<2)
                +(courseScheduling.getSecondSection()<<1)
                +courseScheduling.getFirstSection();
        int week=(courseScheduling.getSunday()<<6)
                +(courseScheduling.getSaturday()<<5)
                +(courseScheduling.getFriday()<<4)
                +(courseScheduling.getThursday()<<3)
                +(courseScheduling.getWednesday()<<2)
                +(courseScheduling.getTuesday()<<1)
                +courseScheduling.getMonday();
        int time=(courseScheduling.getEighthLesson()<<7)
                +(courseScheduling.getSeventhLesson()<<6)
                +(courseScheduling.getSixthLesson()<<5)
                +(courseScheduling.getFifthLesson()<<4)
                +(courseScheduling.getForthLesson()<<3)
                +(courseScheduling.getThirdLesson()<<2)
                +(courseScheduling.getSecondLesson()<<1)
                +courseScheduling.getFirstLesson();
        String SCHEDULE_UPDATE = "UPDATE COURSE_SCHEDULING " +
                "SET RESTRICTION_NUMBER=" + courseScheduling.getRestrictionNumber()
                + ",RESTRICTION_TERM=" +term
                + ",RESTRICTION_WEEK=" +week
                + ",RESTRICTION_TIME=" +time
                + ",RESTRICTION_SECTION=" + section;
        int courseId = courseScheduling.getCourseId();
        if (courseId != 0)
            SCHEDULE_UPDATE += ",COURSE_ID=" + courseId;
        int classroomId = courseScheduling.getClassroomId();
        if (classroomId != 0)
            SCHEDULE_UPDATE += ",CLASSROOM_ID=" + classroomId;
        int teacherId = courseScheduling.getTeacherId();
        if (teacherId != 0)
            SCHEDULE_UPDATE += ",TEACHER_ID=" + teacherId;
        String grade = courseScheduling.getRestrictionGrade();
        if (null != grade && !grade.equals(""))
            SCHEDULE_UPDATE += ",RESTRICTION_GRADE=" + grade;
        else
            SCHEDULE_UPDATE += ",RESTRICTION_GRADE=''";
        SCHEDULE_UPDATE += ",RESTRICTION_REQUIRED=" + courseScheduling.getRequired()
                + " WHERE COURSE_SCHEDULING_ID=" + courseScheduling.getId();

        int i = this.jdbcTemplate.update(SCHEDULE_UPDATE);
        System.out.println("Updated " + i + " row(s)");
    }

    public List<Clasz> getCourseScheduleClasses(int id) {
        String sql = "SELECT A.ID,A.CLASS_NAME,A.CLASS_YEAR,A.CLASS_TYPE FROM CLASS A " +
                "LEFT JOIN COURSE_SCHEDULING_CLASS B ON A.ID=B.CLASS_ID " +
                "WHERE B.COURSE_SCHEDULING_ID=" + id;
        return this.jdbcTemplate.query(sql, new ClaszRowMapper());
    }

    public List<Clasz> getNonCourseScheduleClasses(int id) {
        String sql = "SELECT ID,CLASS_NAME,CLASS_YEAR,CLASS_TYPE FROM CLASS WHERE ID NOT IN(" +
                "SELECT A.ID FROM CLASS A " +
                "LEFT JOIN COURSE_SCHEDULING_CLASS B ON A.ID=B.CLASS_ID " +
                "WHERE B.COURSE_SCHEDULING_ID=" + id + ")";
        return this.jdbcTemplate.query(sql, new ClaszRowMapper());
    }

    public int deleteCourseScheduleClass(int courseScheduleId, int classId) {
        String sql = "DELETE FROM COURSE_SCHEDULING_CLASS WHERE COURSE_SCHEDULING_ID="
                + courseScheduleId + " AND CLASS_ID=" + classId;
        return this.jdbcTemplate.update(sql);
    }

    public int addCourseScheduleClass(int courseScheduleId, int classId) {
        String sql = "INSERT INTO COURSE_SCHEDULING_CLASS (COURSE_SCHEDULING_ID,CLASS_ID)" +
                " VALUES(" + courseScheduleId + "," + classId + ")";
        return this.jdbcTemplate.update(sql);
    }

    private void setCourseSchedulingClassId(CourseScheduling courseScheduling) {
        String CLASS_IDS_QUERY = "SELECT CLASS_ID FROM COURSE_SCHEDULING_CLASS WHERE COURSE_SCHEDULING_ID="
                + courseScheduling.getCourseId();
        List<Integer> classIdList = this.jdbcTemplate.queryForList(CLASS_IDS_QUERY, Integer.class);
        int[] classIds = new int[classIdList.size()];
        //String[]
        for (int i = 0; i < classIdList.size(); i++) {
            classIds[i] = classIdList.get(i);
        }
        //courseScheduling.setClassId(classIds);
    }

    public List<SearchCourse> getSearchCourse() {
        String sql = "SELECT DISTINCT A.COURSE_ID,B.COURSE_NAME FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE B ON A.COURSE_ID=B.ID";
        return this.jdbcTemplate.query(sql, new SearchCourseRowMapper());
    }

    public List<SearchTeacher> getSearchTeacher() {
        String sql = "SELECT DISTINCT A.TEACHER_ID,B.TEACHER_NAME FROM COURSE_SCHEDULING A " +
                "LEFT JOIN TEACHER B ON A.TEACHER_ID=B.ID";
        return this.jdbcTemplate.query(sql, new SearchTeacherRowMapper());
    }

    private class SearchTeacherRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            SearchTeacher searchTeacher = new SearchTeacher();
            searchTeacher.setTeacherId(resultSet.getInt("TEACHER_ID"));
            searchTeacher.setTeacherName(resultSet.getString("TEACHER_NAME"));
            return searchTeacher;
        }
    }

    private class SearchCourseRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            SearchCourse searchCourse = new SearchCourse();
            searchCourse.setCourseId(resultSet.getInt("COURSE_ID"));
            searchCourse.setCourseName(resultSet.getString("COURSE_NAME"));
            return searchCourse;
        }
    }

    private class CourseScheduleRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            CourseScheduling courseScheduling = new CourseScheduling();
            courseScheduling.setId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            courseScheduling.setRequired(resultSet.getBoolean("RESTRICTION_REQUIRED") ? 1 : 0);
            courseScheduling.setRestrictionNumber(resultSet.getInt("RESTRICTION_NUMBER"));
            courseScheduling.setRestrictionGrade(resultSet.getString("RESTRICTION_GRADE"));

            setRestrictionTerm(courseScheduling, resultSet.getInt("RESTRICTION_TERM"));
            setRestrictionSection(courseScheduling, resultSet.getInt("RESTRICTION_SECTION"));
            setRestrictionWeek(courseScheduling, resultSet.getInt("RESTRICTION_WEEK"));
            setRestrictionTime(courseScheduling, resultSet.getInt("RESTRICTION_TIME"));

            courseScheduling.setTeacherId(resultSet.getInt("TEACHER_ID"));
            courseScheduling.setTeacherName(resultSet.getString("TEACHER_NAME"));
            courseScheduling.setClassroomId(resultSet.getInt("CLASSROOM_ID"));
            courseScheduling.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            courseScheduling.setCourseId(resultSet.getInt("COURSE_ID"));
            courseScheduling.setCourseName(resultSet.getString("COURSE_NAME"));

            return courseScheduling;
        }

        private void setRestrictionSection(CourseScheduling courseScheduling, int section) {
            courseScheduling.setFirstSection((section&1)==1?1:0);
            courseScheduling.setSecondSection((section&2)==2?1:0);
            courseScheduling.setThirdSection((section&4)==4?1:0);
        }

        private void setRestrictionTerm(CourseScheduling courseScheduling, int term) {
            courseScheduling.setFirstTerm((term&1)==1?1:0);
            courseScheduling.setSecondTerm((term&2)==2?1:0);
        }

        private void setRestrictionTime(CourseScheduling courseScheduling, int time) {
            courseScheduling.setFirstLesson((time&1)==1?1:0);
            courseScheduling.setSecondLesson((time&2)==2?1:0);
            courseScheduling.setThirdLesson((time&4)==4?1:0);
            courseScheduling.setForthLesson((time&8)==8?1:0);
            courseScheduling.setFifthLesson((time&16)==16?1:0);
            courseScheduling.setSixthLesson((time&32)==32?1:0);
            courseScheduling.setSeventhLesson((time&64)==64?1:0);
            courseScheduling.setEighthLesson((time&128)==128?1:0);
        }

        private void setRestrictionWeek(CourseScheduling courseScheduling, int week) {
            courseScheduling.setMonday((week&1)==1?1:0);
            courseScheduling.setTuesday((week&2)==2?1:0);
            courseScheduling.setWednesday((week&4)==4?1:0);
            courseScheduling.setThursday((week&8)==8?1:0);
            courseScheduling.setFriday((week&16)==16?1:0);
            courseScheduling.setSaturday((week&32)==32?1:0);
            courseScheduling.setSunday((week&64)==64?1:0);
        }
    }

    private class SituationCourseSchedulingRowMapper implements RowMapper{

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            SituationCourseScheduling situationCourseScheduling=new SituationCourseScheduling();
            situationCourseScheduling.setId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            situationCourseScheduling.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            situationCourseScheduling.setCourseName(resultSet.getString("COURSE_NAME"));
            situationCourseScheduling.setRestrictionNumber(resultSet.getInt("RESTRICTION_NUMBER"));
            situationCourseScheduling.setTeacherName(resultSet.getString("TEACHER_NAME"));
            return situationCourseScheduling;
        }
    }

    private class ClaszRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            Clasz clasz = new Clasz();
            clasz.setId(resultSet.getInt("id"));
            clasz.setName(resultSet.getString("CLASS_NAME"));
            clasz.setCreateYear(resultSet.getString("CLASS_YEAR"));
            clasz.setType(resultSet.getInt("CLASS_TYPE"));
            return clasz;
        }
    }
}
